package gu.sql2java.generator;

import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.StringTokenizer;
import java.util.TreeMap;
import java.util.Vector;
import java.util.stream.Collectors;

import com.gitee.l0km.com4j.base.InterfaceDecorator;
import com.google.common.base.Function;
import com.google.common.base.Joiner;
import com.google.common.base.Predicate;
import com.google.common.base.Strings;
import com.google.common.base.Throwables;
import com.google.common.collect.Collections2;
import com.google.common.collect.FluentIterable;
import com.google.common.collect.ImmutableList;
import com.google.common.collect.Iterators;
import com.google.common.collect.Lists;
import com.google.common.collect.Ordering;

import static com.google.common.base.Preconditions.checkState;
import static gu.sql2java.generator.GeneratorConfig.CONFIG;

public class Database {
    private String[] tableTypes;
	private Connection pConnection;
	private DatabaseMetaData meta;
	private Vector<Table> tables;
	private Hashtable<String,Table> tableHash;
	private DatabaseVendor engine;
	private String driver;
	private String url;
	private String username;
	private String password;
	private String catalog;
	private String schema;
	private String tablenamepattern = "%";
	/**
	 * 如果 {@link #tablenamepattern}定义了表名前缀, 
	 * 是否强制使用 {@link #tablenamepattern}定义的前缀作为最小表名前缀
	 * @since 3.23.1
	 */
	private boolean usingTablenamepatternAsSamePrefix = false;
	/**
	 * 所有表名的共同前缀
	 */
	private String samePrefix = "";
	public void setDriver(String driver) {
		this.driver = driver;
	}

	public void setUrl(String url) {
		this.url = url;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public void setCatalog(String catalog) {
		this.catalog = catalog;
	}

	public void setTableNamePattern(String tablenamepattern) {
		if(tablenamepattern != null){
			this.tablenamepattern = tablenamepattern;
		}
	}

	public void setUsingTablenamepatternAsSamePrefix(String usingTablenamepatternAsSamePrefix) {
		try {
			this.usingTablenamepatternAsSamePrefix = Boolean.valueOf(usingTablenamepatternAsSamePrefix);
		} catch (Exception e) {
		}
	}

	public void setTableTypes(String[] tt) {
		this.tableTypes = tt;
	}

	public DatabaseVendor getEngine() {
		return this.engine;
	}

	public String getDriver() {
		return this.driver;
	}

	public String getUrl() {
		return this.url;
	}

	public String getUsername() {
		return this.username;
	}

	public String getPassword() {
		return this.password;
	}

	public String getCatalog() {
		return this.catalog;
	}

	public String getSchema() {
		return this.schema;
	}

	public String getTableNamePattern() {
		return this.tablenamepattern;
	}

	public String[] getTableTypes() {
		return this.tableTypes;
	}

	public void setSchema(String schema) {
		this.schema = "null".equalsIgnoreCase(schema) ? null : schema;
	}

	public Table[] getRelationTable(Table table) {
		Vector<Table> vector = new Vector<Table>();
		for (int iIndex = 0; iIndex < this.tables.size(); ++iIndex) {
			Table tempTable = (Table) this.tables.get(iIndex);
			if (table.equals((Object) tempTable) || !tempTable.isRelationTable() || !tempTable.relationConnectsTo(table)
					|| vector.contains((Object) tempTable))
				continue;
			vector.add(tempTable);
		}
		return vector.toArray(new Table[vector.size()]);
	}
	public void load() throws SQLException, ClassNotFoundException {
		/** 加载并注册JDBC驱动 */
		Class<?> driverClass;
		try {
			driverClass = Class.forName(this.driver);
		} catch (ClassNotFoundException e) {
			driverClass = Class.forName(this.driver,true,CONFIG.getClassloader());
		}
		try {
			DriverManager.registerDriver(
					new InterfaceDecorator<Driver,Driver>(Driver.class,(Driver)driverClass.newInstance())
					.proxyInstance());
		} catch (ReflectiveOperationException e) {
			throw new RuntimeException(e);
		}
		System.out.println("Connecting to " + this.username + " on " + this.url + " ...");
		this.pConnection = DriverManager.getConnection(this.url, this.username, this.password);
		System.out.println("    Connected.");
		try {
			// for oracle connection,set 'remarksReporting' flag to true for read comments
			// 连接为 oracle JDBC 时,调用 setRemarksReporting 设置'remarksReporting'为true,用于读取表中的注释内容
			Class<? extends Connection> connClass = pConnection.getClass();
			if(connClass.getName().equals("oracle.jdbc.driver.OracleConnection")){
				Method method = connClass.getMethod("setRemarksReporting", boolean.class);	
				if(null != method){
					method.invoke(pConnection, true);
				}
			}			
		} catch (Exception e) {
			Throwables.throwIfUnchecked(e);
			throw new RuntimeException(e);
		}
		this.meta = this.pConnection.getMetaData();
		String productName = this.meta.getDatabaseProductName();
		System.out.println("    Database server :" + productName + ".");
		this.engine = DatabaseVendor.fromProductName(productName);
		this.tables = new Vector<Table>();
		this.tableHash = new Hashtable<String,Table>();
		this.loadTables();
		this.initSamePrefix();
		this.loadColumns();
		this.loadPrimaryKeys();
		this.loadImportedKeys();
		this.loadIndexes();
		this.loadProcedures();
		this.sortElements();
		this.pConnection.close();
	}

	public Table[] getTables() {
		return this.tables.toArray(new Table[this.tables.size()]);
	}
	/**
	 * @since 3.31.0
	 */
	public List<Table> getTableSortedList() {
		return FluentIterable.from(getTables()).toSortedList(Ordering.natural().onResultOf(Table::getName));
	}

	
	private void addTable(Table t) {
		this.tables.addElement(t);
		this.tableHash.put(t.getName(), t);
	}

	public Table getTable(String name) {
		return this.tableHash.get(name);
	}
	/**
	 * @since 3.31.0
	 */
	public Table getTableFlexible(String name,String samePrefix) {
		try {
			getTable(name);
			
		} catch (NullPointerException e) {
			throw e;
		}
		Table table = getTable(name);
		if(null == table) {
			Map<String, Table> _tables = this.tables.stream().collect(Collectors.toMap(t->t.getName().replaceFirst(getSamePrefix(), ""), 
					java.util.function.Function.identity()));
			if(null== (table = _tables.get(name)) && !Strings.isNullOrEmpty(samePrefix)) {
				table = _tables.get(name.substring(samePrefix.length()));
			}
		}
		return table;
	}
	private void loadTables() throws SQLException {
		System.out.println("Loading table list according to pattern " + this.tablenamepattern + " ...");
		StringTokenizer st = new StringTokenizer(this.tablenamepattern, ",; \t");
		while (st.hasMoreTokens()) {
			String pattern = st.nextToken().trim();
			String tableSchema = this.schema;
			int index = pattern.indexOf(46);
			if (index > 0) {
				tableSchema = pattern.substring(0, index);
				pattern = pattern.substring(index + 1);
			}
			ResultSet resultSet = this.meta.getTables(this.catalog, tableSchema, pattern, this.tableTypes);
			while (!resultSet.isClosed() && resultSet.next()) {
				Table table = new Table();
				table.setCatalog(resultSet.getString("TABLE_CAT"));
				table.setSchema(resultSet.getString("TABLE_SCHEM"));
				table.setName(resultSet.getString("TABLE_NAME"));
				table.setType(resultSet.getString("TABLE_TYPE"));
				table.setRemarks(resultSet.getString("REMARKS"));
				table.setDatabase(this);
				if (!CodeWriter.authorizeProcess((String) table.getName(), (String) "tables.include",
						(String) "tables.exclude"))
					continue;
				this.addTable(table);
				System.out.println("    table " + table.getName() + " found");
			}
			resultSet.close();
		}
	}

	private void loadColumns() throws SQLException {
		System.out.println("Loading columns ...");
		Iterator<Table> it = this.tables.iterator();
		while (it.hasNext()) {
			Table table = (Table) it.next();
			Column c = null;
			ResultSet resultSet = this.meta.getColumns(table.getCatalog(), table.getSchema(), table.getName(), "%");
			while (!resultSet.isClosed() && resultSet.next()) {
				c = new Column();
				c.setDatabase(this);
				c.setCatalog(resultSet.getString("TABLE_CAT"));
				c.setSchema(resultSet.getString("TABLE_SCHEM"));
				c.setTableName(resultSet.getString("TABLE_NAME"));
				c.setName(resultSet.getString("COLUMN_NAME"));
				c.setType(resultSet.getShort("DATA_TYPE"));
				c.setTypeName(resultSet.getString("TYPE_NAME"));
				c.setSize(resultSet.getInt("COLUMN_SIZE"));
				c.setDecimalDigits(resultSet.getInt("DECIMAL_DIGITS"));
				c.setRadix(resultSet.getInt("NUM_PREC_RADIX"));
				c.setNullable(resultSet.getInt("NULLABLE"));
				c.setRemarks(resultSet.getString("REMARKS"));
				c.setDefaultValue(resultSet.getString("COLUMN_DEF"));
				boolean autoUpdate = engine.detectsAutoUpdateInColumn(pConnection, schema, c.getTableName(), c.getName());
				c.setAutoUpdate(autoUpdate);
				c.setOrdinalPosition(resultSet.getInt("ORDINAL_POSITION"));
				c.setAutoincrement(resultSet.getString("IS_AUTOINCREMENT"));
				System.out.printf("        %s %s(%d) %s default value: %s%s\n", c.getFullName(),
						c.getTypeName(), c.getSize(), c.isAutoincrement() ? "AUTOINCREMENT" : "",
						c.getOriginalDefaultValue(),
						autoUpdate ? ", AUTOUPDATE" : ""
						);
				table.addColumn(c);
				if(c.isAutoincrement())
					table.setAutoincrement(c);
			}
			resultSet.close();
			System.out.println("    " + table.getName() + " found " + table.countColumns() + " columns");
		}
	}

	private void loadPrimaryKeys() throws SQLException {
		System.out.println("Database::loadPrimaryKeys");
		Iterator<Table> it = this.tables.iterator();
		while (it.hasNext()) {
			Column col;
			Table table = (Table) it.next();
			TreeMap<String, Column> map = new TreeMap<String, Column>();
			ResultSet pResultSet = this.meta.getPrimaryKeys(table.getCatalog(), table.getSchema(), table.getName());
			while (!pResultSet.isClosed() && pResultSet.next()) {
				String colName = pResultSet.getString("COLUMN_NAME");
				short seq = pResultSet.getShort("KEY_SEQ");
				System.out.println("Found primary key (seq,name) (" + seq + "," + colName + ") for table '"
						+ table.getName() + "'");
				col = table.getColumn(colName);
				if (col == null)
					continue;
				map.put(String.valueOf(seq), col);
			}
			pResultSet.close();
			int size = map.size();
			for (int k = 1; k <= size; ++k) {
				col = (Column) map.get(String.valueOf(k));
				table.addPrimaryKey(col);
			}
		}
	}

	private void loadImportedKeys() throws SQLException {
		System.out.println("Loading imported keys ...");
		Iterator<Table> it = this.tables.iterator();
		while (it.hasNext()) {
			ResultSet resultSet;
			Table table = (Table) it.next();
			try {
				resultSet = this.meta.getImportedKeys(table.getCatalog(), table.getSchema(), table.getName());
			} catch (SQLException sqle) {
				System.out.println("    Error while loading imported keys for table " + table.getName());
				continue;
			}
			while (!resultSet.isClosed() && resultSet.next()) {
				String tabName = resultSet.getString("FKTABLE_NAME");
				String colName = resultSet.getString("FKCOLUMN_NAME");
				String foreignTabName = resultSet.getString("PKTABLE_NAME");
				String foreignColName = resultSet.getString("PKCOLUMN_NAME");
				String foreignKeyName = resultSet.getString("FK_NAME");
				short updateRule = resultSet.getShort("UPDATE_RULE");
				short deleteRule = resultSet.getShort("DELETE_RULE");
				if(Strings.isNullOrEmpty(foreignKeyName)){
					Vector<Column> primaryKeys = this.getTable(tabName).getPrimaryKeysAsList();
					checkState(!primaryKeys.isEmpty());
					// make a fake name
					String combinName = Joiner.on('_').join(Iterators.transform(primaryKeys.iterator(), new Function<Column,String>(){
						@Override
						public String apply(Column input) {
							return input.getName();
						}}));
					foreignKeyName="fk_"+ tabName + "_" +combinName;
					System.out.println("WARN: FK_NAME return empty,the generated code  may be incorrected.");
				}
					
				short seq = resultSet.getShort("KEY_SEQ");
				Column col = this.getTable(tabName).getColumn(colName);
				Table foreignTable = this.getTable(foreignTabName);
				if (null == foreignTable)
					continue;
				Column foreignCol = foreignTable.getColumn(foreignColName);
				col.addForeignKey(foreignCol, foreignKeyName, seq, 
								Table.ForeignKeyRule.values()[updateRule],
								Table.ForeignKeyRule.values()[deleteRule]);
				foreignCol.addImportedKey(col);
				System.out.println("    " + col.getFullName() + " -> " + foreignCol.getFullName() + " found seq:"+ seq+" foreign key name:"+ foreignKeyName);
				System.out.println("    UPDATE_RULE:" + Table.ForeignKeyRule.values()[updateRule].name() 
						         + "    DELETE_RULE:" + Table.ForeignKeyRule.values()[deleteRule].name());
			}
			resultSet.close();
		}
	}

	private void loadIndexes() throws SQLException {
		System.out.println("Loading indexes ...");
		Iterator<Table> it = this.tables.iterator();
		while (it.hasNext()) {
			Table table = (Table) it.next();
			ResultSet resultSet = null;
			try {
				resultSet = this.meta.getIndexInfo(table.getCatalog(), table.getSchema(), table.getName(), false, true);
			} catch (SQLException sqle) {
				System.out.println("    Error while loading indexes for table " + table.getName());
				continue;
			}
			String currentName = "";
			Index index = null;
			while (!resultSet.isClosed() && resultSet.next()) {
				Column col;
				String colName = resultSet.getString("COLUMN_NAME");
				if (DatabaseVendor.PHOENIX == engine) {
				    int idx;
				    if((idx = colName.lastIndexOf(":")) >= 0){
				        colName = colName.substring(idx + 1);
				    }
				}
				String indName = resultSet.getString("INDEX_NAME");
				if (null == indName || null == colName || (col = table.getColumn(colName)).isPrimaryKey())
					continue;
				if (!currentName.equals(indName)) {
					index = new Index(indName, table);
					index.setUnique(!resultSet.getBoolean("NON_UNIQUE"));
					currentName = indName;
				}
				System.out.println(
						"  Found interesting index " + indName + (index.isUnique()?"(UNIQUE)": "") + " on " + colName + " for table " + table.getName() );

				IndexColumn column = new IndexColumn();
				column.setName(colName);
				column.setOrdinalPosition((int) resultSet.getShort("ORDINAL_POSITION"));
				column.setSortSequence(resultSet.getString("ASC_OR_DESC"));
				column.setFilterCondition(resultSet.getString("FILTER_CONDITION"));
				column.setType(col.getType());
				column.setRemarks(col.getRemarks());
				column.setTableName(col.getTableName());
				column.setDatabase(this);
				index.addIndexColumn(column);
			}
			resultSet.close();
		}
	}

	private void loadProcedures() throws SQLException {
		System.out.println("Loading procedures ...");
		Iterator<Table> it = this.tables.iterator();
		while (it.hasNext()) {
			Table table = (Table) it.next();
			String procedurePattern = "%" + table.getName() + "%";
			ResultSet resultSet = null;
			try {
				resultSet = this.meta.getProcedures(table.getCatalog(), table.getSchema(), procedurePattern);
			} catch (SQLException sqle) {
				System.out.println("    Error while loading procedures for table " + table.getName());
				continue;
			}
			while (!resultSet.isClosed() && resultSet.next()) {
				String spName = resultSet.getString("PROCEDURE_NAME");
				String spRemarks = resultSet.getString("REMARKS");
				Procedure procedure = new Procedure();
				procedure.setName(spName);
				procedure.setRemarks(spRemarks);
				procedure.setReturnType("void");
				table.addProcedure(procedure);
				System.out.println("    Found procedure " + spName + " for table " + table.getName());
				ResultSet rs = this.meta.getProcedureColumns(this.catalog, this.schema, spName, null);
				while (rs.next()) {
					String colName = rs.getString("COLUMN_NAME");
					short columnType = rs.getShort("COLUMN_TYPE");
					if (DatabaseMetaData.procedureColumnUnknown == columnType) {
						System.err.println("    Column " + colName + " of unknown type in procedure " + spName);
						continue;
					}
					Column c = new Column();
					c.setType(rs.getShort("DATA_TYPE"));
					if (5 == columnType) {
						procedure.setReturnType(c.getJavaType());
						continue;
					}
					c.setDatabase(this);
					c.setCatalog(rs.getString("PROCEDURE_CAT"));
					c.setSchema(rs.getString("PROCEDURE_SCHEM"));
					c.setTableName(rs.getString("PROCEDURE_NAME"));
					c.setName(colName);
					c.setSize(rs.getInt("LENGTH"));
					c.setDecimalDigits(rs.getInt("SCALE"));
					c.setRadix(rs.getInt("RADIX"));
					c.setNullable(rs.getInt("NULLABLE"));
					c.setRemarks(rs.getString("REMARKS"));
					switch (columnType) {
						case DatabaseMetaData.procedureColumnIn : {
							procedure.addInColumn(c);
							continue ;
						}
						case DatabaseMetaData.procedureColumnInOut : {
							procedure.addInOutColumn(c);
							continue ;
						}
						case DatabaseMetaData.procedureColumnOut : {
							procedure.addOutColumn(c);
							continue ;
						}
					}
					procedure.setReturnType("List");
				}
				rs.close();
			}
			resultSet.close();
		}
	}

	public String[] getAllPackages() {
		Vector<String> vector = new Vector<String>();
		for (int iIndex = 0; iIndex < this.tables.size(); ++iIndex) {
			Table table = (Table) this.tables.get(iIndex);
			if (vector.contains(table.getPackage()))
				continue;
			vector.add(table.getPackage());
		}
		return vector.toArray(new String[vector.size()]);
	}
	/**
	 * sort foreign keys and Import keys of all column
	 */
	private void sortElements(){
		for(Table table:this.tables){
			for(Column column:table.getColumns()){
				Collections.sort(column.getForeignKeys());
				Collections.sort(column.getImportedKeys());
			}
		}
	}
	
	public String getSamePrefix()  {
		return this.samePrefix;
	}	
	/**
	 * 计算所有表名的共同前缀
	 */
	private void initSamePrefix()  {
		if(usingTablenamepatternAsSamePrefix && this.tablenamepattern.indexOf('%') > 1) {
			samePrefix = this.tablenamepattern.substring(0,this.tablenamepattern.indexOf('%'));
			return;
		}
		List<String> tablenames = this.tables.stream().map(Table::getName).collect(Collectors.toList());
		this.samePrefix = computeSamePrefix(tablenames,this.tablenamepattern);
		System.out.printf("samePrefix = [%s]\n", this.samePrefix);
	}
	/**
	 * 计算所有表名的共同前缀
	 * @since 3.31.0
	 */
	static String computeSamePrefix(Iterable<String> tablenames,String tablenamepattern)  {
		String samePrefix = "";
		if(null != tablenames && tablenames.iterator().hasNext()) {
			List<String> namelist;
			if(tablenames instanceof List) {
				namelist = (List<String>)tablenames;
			}else {
				namelist = Lists.newArrayList(tablenames);
			}
			int index=-1;
			String first = namelist.get(0);
			/* 
			 * 计算所有表名最小长度
			 * 如果表中有下划线，则只返回包含最后一个下划线的长度
			 * 以确保后续计算中得到的前缀长度不能为表名的全部长度
			 */
			int minlen = namelist.stream().map(name->{
						if(name.charAt(name.length()-1)=='_') {
							/** 如果最后字符为'_',则计算时排除结尾的所有'_' */
							name=name.replaceAll("_+$", "");
						}
						int lastUnderline = name.lastIndexOf('_') + 1;
						return (lastUnderline > 0 && lastUnderline < name.length() -1 ) ? lastUnderline: name.length();
					}).reduce((l,r)->Math.min(l, r)).get();
			if(namelist.size()>1){
				try{
					for(int i=0;i<minlen;++i){
						for(int j=1;j<namelist.size();++j){
							String c=namelist.get(j);
							if(c.charAt(i)!=first.charAt(i))
								throw new IndexOutOfBoundsException();
						}
						index=i;
					}
				}catch(IndexOutOfBoundsException e){
				}
				samePrefix= index<0?"":first.substring(0, index+1);
			}else if(tablenamepattern != null){
				int pidx = tablenamepattern.indexOf('%');
				if(pidx>0){
					samePrefix = tablenamepattern.substring(0, pidx);
				}
			}
		}
		return samePrefix;
	}

	public List<Table> getJunctionTables() {
		Table[] tabs = this.getTables();
		ArrayList<Table> tables = new ArrayList<Table>(tabs.length);
		for (int i = 0; i < tabs.length; ++i) {
			if (!tabs[i].isJunctionTable())
				continue;
			tables.add(tabs[i]);
		}
		return tables;
	}
	public List<Table> getTablesWithPk(){
		return Lists.newArrayList(Collections2.filter(ImmutableList.copyOf(getTables()), new Predicate<Table>(){
			@Override
			public boolean apply(Table input) {
				return input.hasPrimaryKey();
			}}));
	}
	/**
	 * 返回主键字段数目为指定值的表
	 */
	public List<Table> getTablesWithPk(final Integer pkLimit){
		return Lists.newArrayList(Collections2.filter(ImmutableList.copyOf(getTables()), new Predicate<Table>(){
			@Override
			public boolean apply(Table input) {
				return input.countPrimaryKeys() == pkLimit;
			}}));
	}
}